package com.stu.swing.dao;

import com.stu.swing.po.Notice;
import com.stu.swing.po.School;
import com.stu.swing.util.DatabaseConnect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NoticeCurd {
    /**
     * 查看所有公告
     *
     * @return
     * @throws Exception
     */
    public static List<Notice> findAll() {
        List<Notice> noticeList = new ArrayList<Notice>();
        DatabaseConnect dbuntil = new DatabaseConnect();
        Connection con = dbuntil.getcon();// 获取数据连接
        String sql = "SELECT * FROM notice ";
        PreparedStatement prestmt=null;
        ResultSet rs=null;
        try {
            prestmt = con.prepareStatement(sql);
            rs = prestmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String content = rs.getString("content");
                Notice notice = new Notice(id, content);
                noticeList.add(notice);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        dbuntil.close(con, prestmt);
        try {
            if(rs!=null) {
                rs.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return noticeList;

    }

    /**
     * 添加公告功能
     *
     * @return
     */
    public static int add(String content)  {
        DatabaseConnect dbuntil = new DatabaseConnect();
        Connection con = dbuntil.getcon();// 获取数据连接
        String sql = "insert into notice values(null,?)";
        PreparedStatement prestmt=null;
        int result=0;
        try {
            prestmt = con.prepareStatement(sql);
            prestmt.setString(1, content);
            result = prestmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        dbuntil.close(con, prestmt);
        return result;
    }

    /**
     * 删除公告功能
     *
     * @return
     */
    public static int delete(String id) {
        DatabaseConnect dbuntil = new DatabaseConnect();
        Connection con = dbuntil.getcon();// 获取数据连接
        String sql = "delete from notice where id=?";
        PreparedStatement prestmt=null;
        int result=0;
        try {
            prestmt = con.prepareStatement(sql);
            prestmt.setString(1, id);
            result = prestmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        dbuntil.close(con, prestmt);
        return result;
    }

    /**
     * 修改公告功能
     * @param school1原来公告名字
     * @param school2后来公告名字
     * @return
     */
    public static int update(String id,String content) {
        DatabaseConnect dbuntil = new DatabaseConnect();
        Connection con = dbuntil.getcon();// 获取数据连接
        String sql = "update notice set content=? WHERE id=?";
        PreparedStatement prestmt=null;
        int result=0;
        try {
            prestmt = con.prepareStatement(sql);
            prestmt.setString(1,content);
            prestmt.setString(2, id);
            result = prestmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        dbuntil.close(con, prestmt);
        return result;
    }

}
